package cn.edu.sdjzu.xg.bysj.dao;


import cn.edu.sdjzu.xg.bysj.domain.Department;
import cn.edu.sdjzu.xg.bysj.domain.Teacher;
import cn.edu.sdjzu.xg.bysj.exception.BysjException;
import cn.edu.sdjzu.xg.bysj.service.DepartmentService;
import cn.edu.sdjzu.xg.bysj.util.Condition;
import cn.edu.sdjzu.xg.bysj.util.JdbcHelper;
import cn.edu.sdjzu.xg.bysj.util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;


public final class TeacherDao {
    private static TeacherDao teacherDao = new TeacherDao();
    private static Collection<Teacher> teachers;

    private TeacherDao() {
    }

    public static TeacherDao getInstance() {
        return teacherDao;
    }


    /**
     * @param connection    数据库连接对象
     * @param conditionList 查询条件
     * @param pagination    分页对象
     * @return
     * @throws SQLException
     */
    public Collection<Teacher> findAll(Connection connection, List<Condition> conditionList,
                                       Pagination pagination) throws SQLException, BysjException {
        //创建Teacher类型集合类对象，HashSet不保证结果中元素的顺序
        Collection<Teacher> desiredTeachers = new HashSet<Teacher>();
        //创建查询教师的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM teacher ");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if (conditionList != null) {
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        //获取Teacher中的元组总数
        int totalNum = CommonDao.getInstance().count(connection, "Teacher", conditionList);

        String paginationClause = pagination.toLimitClause(totalNum);
        sql_select.append(paginationClause);

        //获得查询教师的语句对象
        PreparedStatement pstmt_select =
                connection.prepareStatement(sql_select.toString());
        //获得教师对象的结果集
        ResultSet resultSet_select = pstmt_select.executeQuery();
        //遍历结果集，根据每个元素创建对象，并添加到集合类对象desiredTeachers中
        while(resultSet_select.next()) {
            int id = resultSet_select.getInt("id");
            String name = resultSet_select.getString("name");
            String no = resultSet_select.getString("no");

            int departmentId = resultSet_select.getInt("department_id");
            Department department = DepartmentService.getInstance().find(departmentId);
            Teacher desiredTeacher = new Teacher(id, no, name,"", department);
            desiredTeachers.add(desiredTeacher);
        }
        //关闭ResultSet, statement资源
        JdbcHelper.close(resultSet_select, pstmt_select);
        return desiredTeachers;
    }

    public Teacher find(Connection connection, Integer id) throws SQLException, BysjException {

        //创建查询教师的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM teacher where id = ? ");


        //获得查询教师的语句对象
        PreparedStatement pstmt_select =
                connection.prepareStatement(sql_select.toString());
        pstmt_select.setInt(1,id);
        //获得教师对象的结果集
        ResultSet resultSet_select = pstmt_select.executeQuery();
        Teacher desiredTeacher = null;
        //根据结果集元素创建对象
        if(resultSet_select.next()) {
            String name = resultSet_select.getString("name");
            String no = resultSet_select.getString("no");
            String profTitle = resultSet_select.getString("prof_title");
            int departmentId = resultSet_select.getInt("department_id");
            Department department = DepartmentService.getInstance().find(departmentId);
            desiredTeacher = new Teacher(id, no, name,profTitle, department);
        }
        //关闭ResultSet, statement资源
        JdbcHelper.close(resultSet_select, pstmt_select);
        return desiredTeacher;
    }

}
